package com.zbcbms.common;

import com.zbcbms.entity.vo.BookBorrowVo;
import com.zbcbms.entity.vo.BookVo;
import com.zbcbms.entity.vo.UserVo;
import com.zbcbms.util.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author fengq
 */
public class DaoImplCommon {
    /**
     * 根据id删除记录
     * @param connection
     * @param sql
     * @param id
     * @return
     */
    public static Boolean deleteByCondition(Connection connection, String sql,String id,int isDeleted) {
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,isDeleted);
            preparedStatement.setString(2,id);
            return preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return true;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }


    public static int deleteByIds(Connection connection,List<String> ids,String table,String tableArgs) {
        String sql="UPDATE `"+table+"` SET is_deleted=? " +
                "WHERE "+tableArgs+"= ? and is_deleted=0 ";
        PreparedStatement preparedStatement=null;
        try {
            for (String id:ids){
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,  1);
                preparedStatement.setString(2,id);
                preparedStatement.addBatch();
            }
            int[] ints = preparedStatement.executeBatch();
            preparedStatement.clearBatch();
            return ints.length;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    public static long selectCount(Connection connection,String table,String field,String args) {
        PreparedStatement preparedStatement=null;
        try {
        if (args==null||args==""){
            String sql="select Count(*) as count  from "+table+" where is_deleted=0 ";
            preparedStatement = connection.prepareStatement(sql);
        }else {
            String sql="select Count(*) as count  from "+table+" where is_deleted=0 and "+field+"=? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,args);
        }
            ResultSet resultSet = preparedStatement.executeQuery();
            long total=0;
            while (resultSet.next()) {
                total=resultSet.getLong("count");
            }
            return total;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }

    }

    public long selectBorrowCountByTime(Connection connection,String sql,String args){
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, args);
            ResultSet resultSet = preparedStatement.executeQuery();
            long count=0L;
            while(resultSet.next()){
                count=resultSet.getLong("count");
            }
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0L;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    public static Boolean physicalDeleteByCondition( Connection connection,String sql,String id) {
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            return preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return true;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    public static  List<UserVo> selectAllUser(Connection connection,String sql) {
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery(sql);
            List<UserVo> userVos=new ArrayList<>();
            while (resultSet.next()){
                UserVo userVo=new UserVo();
                userVo.setUserNumber(resultSet.getString("user_number"));
                userVo.setUserName(resultSet.getString("user_name"));
                userVo.setJoinDate(resultSet.getDate("join_date"));
                userVo.setRole(resultSet.getString("role"));
                userVos.add(userVo);
            }
            return userVos;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    public static Boolean recoverById(Connection connection,String sql,String id){
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            return preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    public static List<BookVo> selectAllBook(Connection connection,String sql){
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery(sql);
            List<BookVo> books=new ArrayList<>();
            while(resultSet.next()){
                BookVo bookVo=new BookVo();
                bookVo.setId(resultSet.getString("id"));
                bookVo.setBookName(resultSet.getString("book_Name"));
                bookVo.setBookNumber(resultSet.getString("book_Number"));
                bookVo.setStatus(resultSet.getString("status"));
                bookVo.setPublishingHouse(resultSet.getString("publishing_House"));
                bookVo.setPrice(resultSet.getBigDecimal("price"));
                bookVo.setPublishingTime(resultSet.getDate("publishing_Time"));
                bookVo.setAuthor(resultSet.getString("author"));
                bookVo.setType(resultSet.getString("type"));
                books.add(bookVo);
            }
            return books;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    public static  List<BookBorrowVo> selectAllBorrow(Connection connection,String sql) {
        PreparedStatement preparedStatement =null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<BookBorrowVo> list=new ArrayList<>();
            while(resultSet.next()){
                BookBorrowVo vo = new BookBorrowVo();
                vo.setId(resultSet.getString("id"));
                vo.setBookNumber(resultSet.getString("book_number"));
                vo.setBookName(resultSet.getString("book_name"));
                vo.setBorrowDate(resultSet.getDate("borrow_date"));
                vo.setDeadline(resultSet.getDate("deadline"));
                vo.setReturnDate(resultSet.getDate("return_date"));
                vo.setStatus(resultSet.getString("status"));
                vo.setUserName(resultSet.getString("user_name"));
                vo.setOperatorName(resultSet.getString("operator_name"));
                vo.setUserNumber(resultSet.getString("user_number"));
                list.add(vo);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }


}
